from openpyxl.reader.excel import load_workbook
from builtins import int
import random
import pymysql


#获取工作表数据( cur 是数据库的游标链接，path 是 excel 文件的路径)
def importData(cur, path):
    alllData = []
    # 读取excel文件
    workbook = load_workbook(path)
    # 获得所有工作表的名字
    sheets = workbook.sheetnames
    for sheet in sheets:
        # 获取每一张表
        worksheet = workbook[sheet]
        allData=[]
        # 将表中每一行数据读到 sqlstr 数组中
        for row in worksheet.rows:
            sqlstr = []
            for cell in row:
                sqlstr.append(cell.value)
            allData.append(sqlstr)
        alllData.append(allData)
    return alllData
    

# 格式化数据集
def formatTypeData(data,result):
    # 到第几列
    endcol = 0
    for j in data[0]:
        endcol+=1
        if j == None:
            endcol -= 1
            break
    #跳过第一行 表头
    for i in data[1:]:
        tree1 = []
        length=0
        level=0
        #选取指标列
        for j in i[0:endcol]:
            if(j!=None and length<3):
                tree1.append(j)
                if( length==2):#level==0 and
                    item = {}
                    item["level"] = level
                    item["data"] = tree1
                    result.append(item)
                    level+=1
                    length=0
                    tree1=[]
                    continue
                length += 1
            else:
                # 每三个下降一级
                length += 1
                if (length == 3):
                    level += 1
                    length = 0

# ABCD转换为sortCode
def choiceToSortCode(choice):
    sortCode = 0
    if choice == "A":
        sortCode = 10
    elif choice == "B":
        sortCode = 20
    elif choice == "C":
        sortCode = 30
    elif choice == "D":
        sortCode = 40
    return sortCode

# 格式化数据集
def formatTargetData(data,result):
    # 到第几列
    endcol = 0
    for j in data[0]:
        endcol+=1
        if j == None:
            endcol -= 1
            break
    # 取出所有题
    testMap = {}
    name = ""
    for index,i in enumerate(data[1:]):
        if i[endcol+1:][0] != None:
            name = i[endcol+1:][0]
            testMap[name] = []
        item = []
        item.append(i[endcol+1:][2])
        item.append(i[endcol+1:][3])
        item.append(choiceToSortCode(i[endcol+1:][1]))
        testMap[name].append(item)
    #跳过第一行
    for i in data[1:]:
        tree1 = []
        length=0
        level=0
        #选取指标列
        for j in i[0:endcol]:
            if(j!=None and length<3):
                tree1.append(j)
                if( length==2):#level==0 and
                    # print(tree1)
                    item = {}
                    item["level"] = level
                    item["data"] = tree1
                    if tree1[0] in testMap.keys():
                        item["item"] = testMap[tree1[0]]

                    result.append(item)
                    # print("{}==".format(level),tree1)
                    level+=1
                    length=0
                    tree1=[]
                    continue
                length += 1
            else:
                # 每三个下降一级
                length += 1
                if (length == 3):
                    level += 1
                    length = 0
    
# 连接数据库
def connect(dataSources):
    conn = pymysql.connect(host=dataSources['url'], port=3306, user=dataSources['username'], password=dataSources['password'], db=dataSources['dbname'], charset=dataSources['charset'])
    return conn

# 插入并返回id 
def insertGetId(tb_name,data):
    conn = connect(dataSources)
    # 获取一个光标
    cursor = conn.cursor()
    # 根据参数自动生成sql语句
    col = ''
    value = ''
    for key in data.keys():
        col = col + key +","
        if isinstance(data[key],str):
            value = value + "'"+ data[key] +"',"
        else:
            value = value + str(data[key]) + ","
    sql  = "insert into {tb_name}({col}) values({value});".format(tb_name=tb_name,col=col[0:-1],value=value[0:-1])
    cursor.execute(sql)
    id = conn.insert_id()
    conn.commit()
    # 关闭连接
    cursor.close()
    conn.close()
    return id
# 根据表名查询表
def select(tb_name):
    cursor = connect(dataSources).cursor()
    sql="select * from " + tb_name
    res=cursor.execute(sql)
    evalutetype = []
    for r in cursor.fetchall():
        evalutetype.append(r[0])
    cursor.close()
    return evalutetype

# 根据表名清空表
def delete(tb_name):
    conn = connect(dataSources)
    cursor = conn.cursor()
    print("删除数据-->"+tb_name+"-->开始")
    sql="delete from " + tb_name
    cursor.execute(sql)
    conn.commit()
    cursor.close()
    print("删除数据-->"+tb_name+"<--完成")
    

# 插入评教类型
def insertType(result):
    for item in result:
        tb_name = tbNameList[0]
        data = {
            tbNameCol[tb_name][0]:'{}'.format(item["data"][0]),
            tbNameCol[tb_name][1]:item["data"][2],
            tbNameCol[tb_name][2]:item["data"][1]
        }
        insertGetId(tb_name,data)
# 插入评教指标
def insertTarget(result,evalutetypeId):
    index = 0
    parentId = [-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1]
    for item in result:
        print("插入指标:-->"+item["data"][0]+"-->开始")
        # 获取parentId
        item["data"].append(parentId[item["level"]])
        # 修改最新ID
        tb_name = tbNameList[1]
        data = {
            tbNameCol[tb_name][0]:item["data"][0],
            tbNameCol[tb_name][1]:evalutetypeId,
            tbNameCol[tb_name][2]:item["data"][2],
            tbNameCol[tb_name][3]:parentId[item["level"]],
            tbNameCol[tb_name][4]:item["data"][1]
        }
        id = insertGetId(tb_name,data)
        parentId[item["level"]+1] = id
        item["data"].append(id)
        print("插入指标:->"+item["data"][0]+" <--完成")
        # 插入item 
        if "item" in item.keys():
            print("\t插入试题:-->"+item["data"][0]+"-->开始")
            for iitem in item["item"]:
                tb_name = tbNameList[2]
                data = {
                    tbNameCol[tb_name][0]:iitem[0],
                    tbNameCol[tb_name][1]:id,
                    tbNameCol[tb_name][2]:iitem[1],
                    tbNameCol[tb_name][3]:iitem[2]
                }
                insertGetId(tb_name,data)
            print("\t插入试题:->"+item["data"][0]+" <--完成")

# ===========================================================================
# ======================这里修改配置，其他地方无需修改==========================
# 
# 数据库配置
dataSources = {
    "url":"localhost",
    "port":3306,
    "username":"root",
    "password":"123456",
    "dbname":"tesdb",
    "charset":"utf8"
}
# 对应表名
tbNameList = ["`tes_evalutetype`","`tes_evalutetarget`","`tes_targetitem`"]
# 数据库对应字段名
tbNameCol = {
    tbNameList[0]:["name","weight","sortCode"],
    tbNameList[1]:["name","typeId","weight","parentId","sortCode"],
    tbNameList[2]:["content","targetId","score","sortCode"]
}
# excel路径
baseUrl = r"C:\\Users\\Administrator\\Desktop\\评教指标.xlsx"
# ===========================================================================

if __name__ == '__main__':

    # 导入excel原始数据集
    alllData = importData(None, baseUrl)
    # 格式化原始数据集
    resullt = []
    for i,data in enumerate(alllData):
        result = []
        if i == 0:
            formatTypeData(data,result)
            resullt.append(result)
            continue
        formatTargetData(data,result)
        resullt.append(result)

    # 清空数据库
    
    for tb_name in tbNameList:
        delete(tb_name)
    # id:name对应关系
    evalutetype = {}
    index = 0
    # 根据格式化的结果集操作数据库
    for i,result in enumerate(resullt):
        # 插入类型
        if index == 0:
            insertType(result)
            index = index + 1
            # 插入类型完成，查询类型id:name对应关系
            evalutetype = select(tbNameList[0])
            continue
        insertTarget(result,evalutetype[i-1])